package ext.tianma.util;

import java.io.Closeable;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import wt.util.WTException;

public class ReadExcelUtil {

	/***
	 * 读取excel获取中英文名称
	 * 
	 * @return
	 * @throws WTException
	 */
	public static List<String> getCascade(String STATUTE_PATH, String classificationName) throws WTException {

		System.out.println("===>>start==>>获取二级分类===>>");
		InputStream is = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Row row;
			Cell cell1;
			Cell cell2;
			Cell cell3;

			String tempVal;

			// 第一行为表头，从第二行开始遍历
			for (int i = 1, rowNum = sheet.getLastRowNum(); i <= rowNum; i++) {
				if ((row = sheet.getRow(i)) != null) {
					// 遍历列
					int cellNum = row.getLastCellNum();
					String str = "";
					cell1 = row.getCell(0);
					cell2 = row.getCell(1);
					cell3 = row.getCell(2);
					if (cell1 == null || (tempVal = cell1.getStringCellValue()) == null || cell2 == null
							|| (tempVal = cell2.getStringCellValue()) == null || cell3 == null
							|| (tempVal = cell3.getStringCellValue()) == null) {
						str = "";
					} else {
						str = tempVal.trim();
						System.out.println("===>>str===>>" + str);
						System.out.println("cell1======>>" + cell1.getStringCellValue());
						System.out.println("=========>>>>"+classificationName);
						System.out.println("cell2======>>" + cell2.getStringCellValue());
						System.out.println("cell3======>>" + cell3.getStringCellValue());

						/*
						 * if(classificationName.equals("FPC")) { classificationName = "FPC&PCB"; }
						 */
						if (classificationName.equals(cell1.getStringCellValue())) {
							excelList.add(cell2.getStringCellValue());
						}
					}
				}
			}
			System.out.println("excelList====>>>" + excelList);
			return excelList;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new WTException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new WTException(e);
		} finally {
			close(is);
		}
	}

	
	
	/***
	 * 读取excel获取中英文名称
	 * 
	 * @return
	 * @throws WTException
	 */
	public static String getIda2a2(String STATUTE_PATH, String classificationName) throws WTException {

		System.out.println("===>>start==>>action===>>");
		InputStream is = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			String excelList = "";
			Sheet sheet = workbook.getSheetAt(0);
			Row row;
			Cell cell1;
			Cell cell2;
			Cell cell3;

			String tempVal;

			// 第一行为表头，从第二行开始遍历
			for (int i = 1, rowNum = sheet.getLastRowNum(); i <= rowNum; i++) {
				if ((row = sheet.getRow(i)) != null) {
					// 遍历列
					int cellNum = row.getLastCellNum();
					String str = "";
					cell1 = row.getCell(0);
					cell2 = row.getCell(1);
					cell3 = row.getCell(2);
					if (cell1 == null || (tempVal = cell1.getStringCellValue()) == null || cell2 == null
							|| (tempVal = cell2.getStringCellValue()) == null || cell3 == null
							|| (tempVal = cell3.getStringCellValue()) == null) {
						str = "";
					} else {
						str = tempVal.trim();
						System.out.println("===>>str===>>" + str);
						System.out.println("cell1======>>" + cell1.getStringCellValue());
						System.out.println("cell2======>>" + cell2.getStringCellValue());
						System.out.println("cell3======>>" + cell3.getStringCellValue());

						if (classificationName.equals(cell2.getStringCellValue())) {
							excelList=cell3.getStringCellValue();
						}
					}
				}
			}
			System.out.println("excelList====>>>" + excelList);
			return excelList;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new WTException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new WTException(e);
		} finally {
			close(is);
		}
	}
	
	
	
	public static List<String> getBomIbaVlaue(String STATUTE_PATH, String classificationName) throws WTException {

		System.out.println("===>>start==>>获取bom屬性===>>");
		InputStream is = null;
		Workbook wb = null;
		Workbook workbook = null;
		try {
			System.out.println("CASCADE_PATH===>>" + STATUTE_PATH);
			is = new FileInputStream(STATUTE_PATH);

			System.out.println("=====>>>" + STATUTE_PATH);
			if (!is.markSupported()) {
				is = new PushbackInputStream(is, 8);
			}

			if (POIFSFileSystem.hasPOIFSHeader(is)) {
				// 2003及以下
				wb = new HSSFWorkbook(is);
			} else if (POIXMLDocument.hasOOXMLHeader(is)) {
				// 2007及以上
				wb = new XSSFWorkbook(is);
			} else {
				// 不是Excel
				throw new WTException("Incorrect configuration file path.");
			}

			workbook = wb;

			List<String> excelList = new ArrayList<String>();
			Sheet sheet = workbook.getSheetAt(0);
			Row row;
			Cell cell1;
			Cell cell2;
			Cell cell3;

			String tempVal;

			// 第一行为表头，从第二行开始遍历
			for (int i = 1, rowNum = sheet.getLastRowNum(); i <= rowNum; i++) {
				if ((row = sheet.getRow(i)) != null) {
					// 遍历列
					int cellNum = row.getLastCellNum();
					String str = "";
					cell1 = row.getCell(0);
					cell2 = row.getCell(1);
					cell3 = row.getCell(2);
					if (cell1 == null || (tempVal = cell1.getStringCellValue()) == null || cell2 == null
							|| (tempVal = cell2.getStringCellValue()) == null || cell3 == null
							|| (tempVal = cell3.getStringCellValue()) == null) {
						str = "";
					} else {
						str = tempVal.trim();
						System.out.println("===>>str===>>" + str);
						if(row.getCell(3).getStringCellValue()!=null) {
							excelList.add("长（mm）");
						}
						if(row.getCell(4).getStringCellValue()!=null) {
							excelList.add("宽(mm)");
						}
						if(row.getCell(5).getStringCellValue()!=null) {
							excelList.add("长宽比例");
						}
						if(row.getCell(6).getStringCellValue()!=null) {
							excelList.add("材料厚度(mm)");
						}
						if(row.getCell(7).getStringCellValue()!=null) {
							excelList.add("数量");
						}
						if(row.getCell(8).getStringCellValue()!=null) {
							excelList.add("补充信息");
						}
						if(row.getCell(9).getStringCellValue()!=null) {
							excelList.add("表面镀膜工艺");
						}
						if(row.getCell(10).getStringCellValue()!=null) {
							excelList.add("钢化工艺");
						}
						if(row.getCell(11).getStringCellValue()!=null) {
							excelList.add("外形工艺");
						}
						if(row.getCell(12).getStringCellValue()!=null) {
							excelList.add("是否抛光");
						}
						if(row.getCell(13).getStringCellValue()!=null) {
							excelList.add("孔数");
						}
						if(row.getCell(14).getStringCellValue()!=null) {
							excelList.add("层数");
						}
						if(row.getCell(15).getStringCellValue()!=null) {
							excelList.add("形状");
						}

//						if (classificationName.equals(cell1.getStringCellValue())) {
//							excelList.add(cell2.getStringCellValue());
//						}
					}
				}
			}
			System.out.println("excelList====>>>" + excelList);
			return excelList;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new WTException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new WTException(e);
		} finally {
			close(is);
		}
	}

	
	
	
	
	
	
	
	
	
	
	
	
	

	/**
	 * 关闭资源
	 * 
	 * @param closeable
	 */
	public static void close(Closeable closeable) {
		try {
			if (closeable != null) {
				closeable.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void ReadExcel() {

	}
}
